/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package DBConnections;

/**
 *
 * @author aeguzkiza
 */

import java.sql.*;
import java.util.ArrayList;

public class MySQL_Query {

    private Connection conn;      
    private ResultSet rsMax;
    private ResultSet rsQuery;
    private ResultSet rsUpdate;
    private Statement stmt = null;

    private static String ip = "localhost";
    private static String port = "3306";
    private static String db_name = "dicomizer";
    private static String user = "dicomuser";
    private static String passwd = "dicompass";


    public MySQL_Query(){
        try{            
            //System.out.println("Loading driver...");
            Class.forName("org.drizzle.jdbc.DrizzleDriver");        
            //System.out.println("Driver loaded!");
            
            //System.out.println("Connecting database...");
            conn = DriverManager.getConnection("jdbc:drizzle://"+ip+":"+port+"/"+db_name, user, passwd);    
            //System.out.println("Database connected!"); 
                                  
                    
            stmt = (Statement) conn.createStatement();
        }

        catch(Exception e)
        {
             System.out.println("Error al conectar con la base de datos");
             System.out.println(e.getMessage());
             System.exit(0);
        }


    }
    public void closeConnection(){
        try{
            conn.close();
        }catch(Exception e){
             System.out.println("Error al desconectar de la base de datos");
             System.out.println(e.getMessage());
             System.exit(0);
        }
    }
    public int GetMax(String table) throws SQLException{
        int ID = 0;
        //try {
            rsMax = stmt.executeQuery("select count(1) from "+table+";");
            rsMax.next();
            ID = rsMax.getInt(1);
        /*} catch (SQLException ex) {
            System.out.println(ex);
        }*/
        return ID;
    }
    
    public ResultSet SQLquery( String StrSql ) throws SQLException{
        //try{
            System.out.println("--------------------------------------------");
            System.out.print("( " + StrSql + " )");
            System.out.print(" Only First and Last Rows ");
            System.out.println("  ");

            rsQuery = (ResultSet) stmt.executeQuery(StrSql);
            System.out.println("--------------------------------------------");

        /*             
        // SHOW TABLE HEADER .    
            ResultSetMetaData rsMetaData = rsQuery.getMetaData();
            int numberOfColumns = rsMetaData.getColumnCount();

        for (int i = 1; i < numberOfColumns + 1; i++){
              String columnName = rsMetaData.getColumnName(i);
              System.out.print("- " + columnName + " ");
        }
        
        // SHOW ADQUIRED DATA.
        System.out.print("-");
        System.out.println("");
        System.out.println("--------------------------------------------");

            while(rsQuery.next()){  // Show first and last row value from database query.
                for (int i = 1; i < numberOfColumns + 1; i++){
                    String columnName = rsMetaData.getColumnName(i);
                    System.out.print( "[ "+columnName + ": ");
                System.out.print( rsQuery.getString(columnName) + "] ");
                }

                rsQuery.last();
                System.out.println("");

                for (int i = 1; i < numberOfColumns + 1; i++)
                {
                String columnName = rsMetaData.getColumnName(i);
                System.out.print( "[ "+columnName + ": ");
                System.out.print( rsQuery.getString(columnName) + "] ");
                }
                System.out.println("");
            }

        System.out.println("");
        System.out.println("--------------------------------------------");*/

        /*}catch(Exception e){
           System.out.println("Error en la base de datos");
           System.out.println(e.getMessage());
        }*/
    return rsQuery;
    }

    public ArrayList<String> getColumnNames(ResultSet rs) throws SQLException{

        ResultSetMetaData metadata = rs.getMetaData();
        ArrayList<String> columnNames = new ArrayList();
        
        rs.beforeFirst();
        int n = metadata.getColumnCount();

        for (int i = 0; i < n; i++ ) {
            String cname = metadata.getColumnName(i+1);
            columnNames.add(cname);
        }
        return columnNames;
    }

    public void SQLupdate( String StrSql, boolean IsGetGeneratedKeys, int numvar, Object[] var) throws SQLException
    {        
    //try{
       System.out.println("--------------------------------------------");
       System.out.print("( " + StrSql + " )");
       System.out.print(" Only First and Last Rows ");
       System.out.println("--------------------------------------------");
       PreparedStatement ps;
            if(IsGetGeneratedKeys == true){
                ps = conn.prepareStatement(StrSql,PreparedStatement.RETURN_GENERATED_KEYS);
            }else{
                ps = conn.prepareStatement(StrSql);
            }
            if(numvar != 0){    // Complex statements.
                for(int i=0;i<=numvar-1;i++){ 
                    //System.out.println("i"+i);
                    //System.out.println("var"+var[i]);
                    ps.setObject(i+1, var[i]);
                    
                }
            }
            ps.executeUpdate();
            rsUpdate = ps.getGeneratedKeys();            
            //System.out.println("GeneratedKeyValue: "+rsUpdate.getString(1));
               
       
    /*}catch (SQLException e){
       System.out.println("Error en la base de datos");
       System.out.println(e.getMessage());
     }*/

    }
    public void SQLupdate2( String StrSql, boolean IsGetGeneratedKeys, int numvar, ArrayList var) throws SQLException
    {
    //try{
       System.out.println("--------------------------------------------");
       System.out.print("( " + StrSql + " )");
       System.out.print(" Only First and Last Rows ");
       System.out.println("--------------------------------------------");
       PreparedStatement ps;
            if(IsGetGeneratedKeys == true){
                ps = conn.prepareStatement(StrSql,PreparedStatement.RETURN_GENERATED_KEYS);
            }else{
                ps = conn.prepareStatement(StrSql);
            }
            if(numvar != 0){    // Complex statements.
                for(int i=0;i<=numvar-1;i++){
                    //System.out.println("i"+i);
                    //System.out.println("var"+var[i]);
                    ps.setObject(i+1, var.get(i));

                }
            }
            ps.executeUpdate();
            rsUpdate = ps.getGeneratedKeys();


    }

    public ResultSet GeneratedKeys(){
        //System.out.println("rs update: "+rsUpdate);
        return rsUpdate;
    }

}
